package com.eml.service.util;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
 * JDBC 工具类
 * @author Vantro
 */
public class JDBCUtils {
    /**
     * 创建链接
     */
    public static Connection getConnection() throws Exception {
        // druid操作
        // ① 读取配置信息
        // 注意：路径前缀/，表示的资源路径
        InputStream resourceAsStream = JDBCUtils.class.getResourceAsStream("/db.properties");
        Properties properties = new Properties();
        properties.load(resourceAsStream);
        // ② 创建数据库连接池（数据源）
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        // ③ 获得连接
        Connection connection = dataSource.getConnection();
        return connection;
    }

    /**
     * 关闭资源
     */
    public static void close(Connection conn, Statement smt) throws SQLException {
        smt.close();
        conn.close();
    }

    public static void close(Connection conn, Statement smt, ResultSet rs) throws SQLException {
        rs.close();
        smt.close();
        conn.close();
    }
    /**
     * 查询操作
     * */
    public static ResultSet query(String sql,Object...params) throws Exception{
        //获取数据库连接
        Connection connection = getConnection();
        //创建预处理语句
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //为预处理语句设置参数
            for (int i = 0; i < params.length; i++) {
                preparedStatement.setObject(i+1,params[i]);
            }
        //执行查询操作并返回结果集
        return preparedStatement.executeQuery();
    }
    /**
     *批量插入
     * */
    public static void batchInsert(String sql,Object[][] params) throws Exception {
        //获取数据库连接
        Connection connection = getConnection();
        //设置为手动提交事务
        connection.setAutoCommit(false);
        //创建预处理语句
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //为批量插入设置参数并添加到批处理中
        for (Object[] param :
                params) {
            for (int i = 0; i < params.length; i++) {
                preparedStatement.setObject(i+1,param[i]);
            }
            preparedStatement.addBatch();
        }
        //执行批处理操作
        preparedStatement.executeBatch();
        //提交事务
        connection.commit();
    }
}
